forum

home / developersection / forums / how to get cumulative frequency in sql server

Ask Question

How to get Cumulative frequency in SQL Server

Anonymous User 6487 10-Sep-2012
Hello Everyone,
I've a small requirement; Actually I've a table which has three column name such as ID, Product_Name and Product_Quantity, these columns contains value like:
ID                         Product_Name                     Product_Quantity
1                              Parle G                                     10
2                              Hair Oil                                    20
3                              Soap                                         30
4                              Shampoo                                  49

In this table I want to add another column name like Product_CumulativeValue which contains cumulative value of Product_Quantity column, so can you tell me how to find out the cumulative value of Product_Quantity column.

Please resolve my problem as soon as possible.
Thanks in advance!
mssql server mssql server 
Updated on 11-Sep-2012
Anonymous User

Other

I am a content writter !

Message
Can you answer this question?
Answer

2 Answers

Anonymous User

Anonymous User

11-Sep-2012
Hi AVADHESH PATEL,
Hurrah!!! This code resolve my problem. Thank you very much Avadhesh. Keep it up.
AVADHESH PATEL

AVADHESH PATEL

10-Sep-2012
--Calculate Cumulative Frequency in SQL Server

--Create Database
CREATE DATABASE [AldSalesPvtLtd]
GO

--Use Database
USE [AldSalesPvtLtd]
GO

--Create Table
CREATE TABLE [dbo].[Product]
(
[ID] INT PRIMARY KEY,
[Product_Name] VARCHAR(50) NOT NULL,
[Product_Quantity] INT NOT NULL
)
GO

--Insert Record into Student Table
INSERT INTO [dbo].[Product]([ID],[Product_Name],[Product_Quantity])
SELECT 1,'Parle G',10
UNION ALL
SELECT 2,'Hair Oil',20
UNION ALL 
SELECT 3,'Soap',30
UNION ALL
SELECT 4,'Shampoo',40
GO

-- Display Record of Student Table
SELECT [ID],[Product_Name],[Product_Quantity] FROM [dbo].[Product]
GO

-- Create Temporary Table
CREATE TABLE #Product
(
[ID] INT PRIMARY KEY,
[Product_Name] VARCHAR(50) NOT NULL,
[Product_Quantity] INT NOT NULL,
[Product_CumulativeValue] INT NOT NULL --[Product_CumulativeValue] column Stored Calculated Cumulative Frequency result
)
GO

-- Declare Variables and set values
DECLARE @ID INT,
@Product_Name VARCHAR(50),
@Product_Quantity INT,
        @Product_CumulativeValue INT
SET @Product_CumulativeValue = 0

-- Create Corsor
DECLARE CF_Cursor CURSOR
FOR
SELECT [ID],[Product_Name],[Product_Quantity]
FROM [dbo].[Product]

--Open Corsor
OPEN CF_Cursor

-- Select Record from cursor and stored into variables
FETCH NEXT FROM CF_Cursor INTO @ID, @Product_Name,@Product_Quantity

--@@FETCH_STATUS Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
WHILE @@FETCH_STATUS = 0 -- 0 indicate FETCH statement was successful.
 BEGIN
  SET @Product_CumulativeValue = @Product_CumulativeValue + @Product_Quantity
  INSERT #Product VALUES (@ID,@Product_Name,@Product_Quantity,@Product_CumulativeValue)
  FETCH NEXT FROM CF_Cursor INTO @ID, @Product_Name,@Product_Quantity
 END

-- Close Cursor
CLOSE CF_Cursor
-- Deallocate Cursor
DEALLOCATE CF_Cursor

-- Select * from Temporary Table #Student
SELECT * FROM #Product
GO
advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy